package com.xinyirun.scm.core.system.mapper.sys.schedule.v2;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xinyirun.scm.bean.api.vo.sync.ApiBMaterialPriceVo;
import com.xinyirun.scm.bean.entity.business.price.BMaterialPriceEntity;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 关于库存日报表的mapper
 */
@Repository
public interface SBMaterialPriceV2Mappper extends BaseMapper<BMaterialPriceEntity> {

    /**
     * 删除当天数据
     */
    @Delete(""
            + "DELETE                                                                                                   "
            + "FROM                                                                                                     "
            + " b_material_daily_price t                                                                                "
            + "WHERE                                                                                                    "
            + " DATE_FORMAT( t.dt, '%Y%m%d' ) = DATE_FORMAT( DATE_SUB(now(), INTERVAL 1 DAY ), '%Y%m%d' )               "
    +"")
    public void deleteIntradayData00();

    /**
     * 删除当天数据
     */
    @Delete(""
            + "DELETE                                                                                                   "
            + "FROM                                                                                                     "
            + " b_material_price t                                                                                      "
            +"")
    public void deleteData01();


    // 转换后物料单价 b_material_convert_price
    @Update({""
            + "	insert into b_material_convert_price(                                                                                              "
            + "	    dt,                                                                                                                            "
            + "	    goods_id,                                                                                                                      "
            + "	    goods_code,                                                                                                                    "
            + "	    goods_name,                                                                                                                    "
            + "	    sku_id,                                                                                                                        "
            + "	    sku_code,                                                                                                                      "
            + "	    sku_name,                                                                                                                      "
            + "	    source_goods_id,                                                                                                               "
            + "	    source_goods_code,                                                                                                             "
            + "	    source_goods_name,                                                                                                             "
            + "	    source_sku_id,                                                                                                                 "
            + "	    source_sku_code,                                                                                                               "
            + "	    source_sku_name,                                                                                                               "
            + "	    price,                                                                                                                         "
            + "	    c_time)                                                                                                                        "
            + "	SELECT                                                                                                                             "
            + "		DATE_SUB(now(), INTERVAL 1 DAY ) dt,                                                                                           "
            + "		t6.id goods_id,                                                                                                                "
            + "		t6.CODE goods_code,                                                                                                            "
            + "		t6.NAME goods_name,                                                                                                            "
            + "		t5.id sku_id,                                                                                                                  "
            + "		t5.CODE sku_code,                                                                                                              "
            + "		t5.spec sku_name,                                                                                                              "
            + "		t4.id source_goods_id,                                                                                                         "
            + "		t4.CODE source_goods_code,                                                                                                     "
            + "		t4.NAME source_goods_name,                                                                                                     "
            + "		t3.id source_sku_id,                                                                                                           "
            + "		t3.CODE source_sku_code,                                                                                                       "
            + "		t3.spec source_sku_name,                                                                                                       "
            + "		sum(t7.amount)/sum(t7.qty) price,                                                                                              "
            + "		NOW() c_time                                                                                                                   "
            + "	FROM                                                                                                                               "
            + "		b_material_convert_detail t1                                                                                                   "
            + "		LEFT JOIN b_material_convert t2 ON t1.material_convert_id = t2.id                                                              "
            + "		LEFT JOIN m_goods_spec t3 ON t3.id = t1.source_sku_id                                                                          "
            + "		LEFT JOIN m_goods t4 ON t3.goods_id = t4.id                                                                                    "
            + "		LEFT JOIN m_goods_spec t5 ON t5.id = t1.target_sku_id                                                                          "
            + "		LEFT JOIN m_goods t6 ON t5.goods_id = t6.id                                                                                    "
            + "		LEFT JOIN (                                                                                                                    "
            + "		SELECT                                                                                                                         "
            + "			DATE_FORMAT( t.u_time, '%Y-%m-%d' ) dt,                                                                                    "
            + "			t.sku_id,                                                                                                                  "
            + "			sum( ifnull( t.actual_weight, 0 ) ) qty,                                                                                   "
            + "			sum( t.actual_weight * ifnull( t.price, 0 ) ) amount                                                                       "
            + "		FROM                                                                                                                           "
            + "			b_in t                                                                                                                     "
            + "		GROUP BY                                                                                                                       "
            + "			t.sku_id,                                                                                                                  "
            + "			DATE_FORMAT( t.u_time, '%Y-%m-%d' )                                                                                        "
            + "		) t7 ON t1.source_sku_id = t7.sku_id                                                                                           "
            + "		AND DATE_FORMAT( t7.dt, '%Y-%m-%d' ) >= DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 15 DAY ), '%Y-%m-%d' )                          "
            + "	    GROUP BY t1.target_sku_id                                                                                                      "
    })
    public void insertMaterialPrice10();

    // 转换前物料单价 b_material_convert_price
    @Update({""
            + "	insert into b_material_convert_price(                                                                                              "
            + "	    dt,                                                                                                                            "
            + "	    goods_id,                                                                                                                      "
            + "	    goods_code,                                                                                                                    "
            + "	    goods_name,                                                                                                                    "
            + "	    sku_id,                                                                                                                        "
            + "	    sku_code,                                                                                                                      "
            + "	    sku_name,                                                                                                                      "
            + "	    source_goods_id,                                                                                                               "
            + "	    source_goods_code,                                                                                                             "
            + "	    source_goods_name,                                                                                                             "
            + "	    source_sku_id,                                                                                                                 "
            + "	    source_sku_code,                                                                                                               "
            + "	    source_sku_name,                                                                                                               "
            + "	    price,                                                                                                                         "
            + "	    c_time)                                                                                                                        "
            + "	SELECT                                                                                                                             "
            + "		DATE_SUB(now(), INTERVAL 1 DAY ) dt,                                                                                           "
            + "		t4.id goods_id,                                                                                                                "
            + "		t4.CODE goods_code,                                                                                                            "
            + "		t4.NAME goods_name,                                                                                                            "
            + "		t3.id sku_id,                                                                                                                  "
            + "		t3.CODE sku_code,                                                                                                              "
            + "		t3.spec sku_name,                                                                                                              "
            + "		t4.id source_goods_id,                                                                                                         "
            + "		t4.CODE source_goods_code,                                                                                                     "
            + "		t4.NAME source_goods_name,                                                                                                     "
            + "		t3.id source_sku_id,                                                                                                           "
            + "		t3.CODE source_sku_code,                                                                                                       "
            + "		t3.spec source_sku_name,                                                                                                       "
            + "		sum(t7.amount)/sum(t7.qty) price,                                                                                              "
            + "		NOW() c_time                                                                                                                   "
            + "	FROM                                                                                                                               "
            + "		b_material_convert_detail t1                                                                                                   "
            + "		LEFT JOIN b_material_convert t2 ON t1.material_convert_id = t2.id                                                              "
            + "		LEFT JOIN m_goods_spec t3 ON t3.id = t1.source_sku_id                                                                          "
            + "		LEFT JOIN m_goods t4 ON t3.goods_id = t4.id                                                                                    "
            + "		LEFT JOIN m_goods_spec t5 ON t5.id = t1.target_sku_id                                                                          "
            + "		LEFT JOIN m_goods t6 ON t5.goods_id = t6.id                                                                                    "
            + "		LEFT JOIN (                                                                                                                    "
            + "		SELECT                                                                                                                         "
            + "			DATE_FORMAT( t.u_time, '%Y-%m-%d' ) dt,                                                                                    "
            + "			t.sku_id,                                                                                                                  "
            + "			sum( ifnull( t.actual_weight, 0 ) ) qty,                                                                                   "
            + "			sum( t.actual_weight * ifnull( t.price, 0 ) ) amount                                                                       "
            + "		FROM                                                                                                                           "
            + "			b_in t                                                                                                                     "
            + "		GROUP BY                                                                                                                       "
            + "			t.sku_id,                                                                                                                  "
            + "			DATE_FORMAT( t.u_time, '%Y-%m-%d' )                                                                                        "
            + "		) t7 ON t1.source_sku_id = t7.sku_id                                                                                           "
            + "		AND DATE_FORMAT( t7.dt, '%Y-%m-%d' ) >= DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 15 DAY ), '%Y-%m-%d' )                          "
            + "	GROUP BY t1.source_sku_id                                                                                                          "
    })
    public void insertMaterialPrice11();

    // 原材料,辅料单价 b_material_convert_price
    @Update({""
            + "	insert into b_material_convert_price(                                                                                              "
            + "	    dt,                                                                                                                            "
            + "	    goods_id,                                                                                                                      "
            + "	    goods_code,                                                                                                                    "
            + "	    goods_name,                                                                                                                    "
            + "	    sku_id,                                                                                                                        "
            + "	    sku_code,                                                                                                                      "
            + "	    sku_name,                                                                                                                      "
            + "	    source_goods_id,                                                                                                               "
            + "	    source_goods_code,                                                                                                             "
            + "	    source_goods_name,                                                                                                             "
            + "	    source_sku_id,                                                                                                                 "
            + "	    source_sku_code,                                                                                                               "
            + "	    source_sku_name,                                                                                                               "
            + "	    price,                                                                                                                         "
            + "	    c_time)                                                                                                                        "
            + "	SELECT                                                                                                                             "
            + "		NOW() dt,                                                                                                                      "
            + "		t2.id goods_id,                                                                                                                "
            + "		t2.CODE goods_code,                                                                                                            "
            + "		t2.NAME goods_name,                                                                                                            "
            + "		t1.id sku_id,                                                                                                                  "
            + "		t1.CODE sku_code,                                                                                                              "
            + "		t1.spec sku_name,                                                                                                              "
            + "		t2.id source_goods_id,                                                                                                         "
            + "		t2.CODE source_goods_code,                                                                                                     "
            + "		t2.NAME source_goods_name,                                                                                                     "
            + "		t1.id source_sku_id,                                                                                                           "
            + "		t1.CODE source_sku_code,                                                                                                       "
            + "		t1.spec source_sku_name,                                                                                                       "
            + "		sum( t4.amount )/ sum( t4.qty ) price,                                                                                         "
            + "		NOW() c_time                                                                                                                   "
            + "	FROM                                                                                                                               "
            + "		m_goods_spec t1                                                                                                                "
            + "		LEFT JOIN m_goods t2 ON t1.goods_id = t2.id                                                                                    "
            + "		LEFT JOIN m_goods_spec_prop t3 ON t1.prop_id = t3.id                                                                           "
            + "		LEFT JOIN (                                                                                                                    "
            + "		SELECT                                                                                                                         "
            + "			DATE_FORMAT( t.u_time, '%Y-%m-%d' ) dt,                                                                                    "
            + "			t.sku_id,                                                                                                                  "
            + "			sum( ifnull( t.actual_weight, 0 ) ) qty,                                                                                   "
            + "			sum( t.actual_weight * ifnull( t.price, 0 ) ) amount                                                                       "
            + "		FROM                                                                                                                           "
            + "			b_in t                                                                                                                     "
            + "		GROUP BY                                                                                                                       "
            + "			t.sku_id,                                                                                                                  "
            + "			DATE_FORMAT( t.u_time, '%Y-%m-%d' )                                                                                        "
            + "		) t4 ON t1.id = t4.sku_id                                                                                                      "
            + "		AND DATE_FORMAT( t4.dt, '%Y-%m-%d' ) >= DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 15 DAY ), '%Y-%m-%d' )                          "
            + "	WHERE                                                                                                                              "
            + "		t3.CODE IN ( '0', '3' )                                                                                                        "
            + "		AND NOT EXISTS (                                                                                                               "
            + "		SELECT                                                                                                                         "
            + "			1                                                                                                                          "
            + "		FROM                                                                                                                           "
            + "			b_material_convert_price tt1                                                                                               "
            + "		WHERE                                                                                                                          "
            + "			t1.id = tt1.source_sku_id                                                                                                  "
            + "		OR t1.id = tt1.sku_id                                                                                                          "
            + "		)                                                                                                                              "
            + "		GROUP BY t1.id                                                                                                                 "
    })
    public void insertMaterialPrice12();

    // 每日商品价格 b_material_daily_price
    @Update({""
            + "	insert into b_material_daily_price(                                                                                                "
            + "		dt,                                                                                                                            "
            + "		goods_id,                                                                                                                      "
            + "		goods_code,                                                                                                                    "
            + "		goods_name,                                                                                                                    "
            + "		sku_id,                                                                                                                        "
            + "		sku_code,                                                                                                                      "
            + "		sku_name,                                                                                                                      "
            + "		type,                                                                                                                          "
            + "		query_code,                                                                                                                    "
            + "		price,                                                                                                                         "
            + "		c_time )                                                                                                                       "
            + "	SELECT                                                                                                                             "
            + "		NOW() dt,                                                                                                                      "
            + "		t1.goods_id,                                                                                                                   "
            + "		t2.CODE goods_code,                                                                                                            "
            + "		t2.NAME goods_name,                                                                                                            "
            + "		t1.id sku_id,                                                                                                                  "
            + "		t1.CODE sku_code,                                                                                                              "
            + "		t1.spec sku_name,                                                                                                              "
            + "	CASE                                                                                                                               "
            + "			WHEN t4.source_sku_id IS NULL THEN                                                                                         "
            + "			'2'                                                                                                                        "
            + "			WHEN t4.source_sku_id = t4.sku_id THEN                                                                                     "
            + "			'0'                                                                                                                        "
            + "			WHEN t4.source_sku_id <> t4.sku_id THEN                                                                                    "
            + "			'1'                                                                                                                        "
            + "		END type,                                                                                                                      "
            + "	CASE	                                                                                                                           "
            + "			WHEN t4.source_sku_id IS NULL THEN                                                                                         "
            + "			t4.CODE                                                                                                                    "
            + "			WHEN t4.source_sku_id = t4.sku_id THEN                                                                                     "
            + "			t4.CODE                                                                                                                    "
            + "			WHEN t4.source_sku_id <> t4.sku_id THEN                                                                                    "
            + "			t5.CODE                                                                                                                    "
            + "		END query_code,                                                                                                                "
            + "	CASE                                                                                                                               "
            + "			WHEN t4.source_sku_id IS NULL THEN                                                                                         "
            + "			t5.price                                                                                                                   "
            + "			WHEN t4.source_sku_id = t4.sku_id THEN                                                                                     "
            + "			t4.price                                                                                                                   "
            + "			WHEN t4.source_sku_id <> t4.sku_id THEN                                                                                    "
            + "			t4.price                                                                                                                   "
            + "		END price,                                                                                                                     "
            + "		NOW() c_time                                                                                                                   "
            + "	FROM                                                                                                                               "
            + "		m_goods_spec t1                                                                                                                "
            + "		LEFT JOIN m_goods t2 ON t1.goods_id = t2.id                                                                                    "
            + "		LEFT JOIN m_goods_spec_prop t3 ON t1.prop_id = t3.id                                                                           "
            + "		LEFT JOIN b_material_convert_price t4 ON t1.id = t4.sku_id                                                                     "
            + "		AND DATE_FORMAT( t4.dt, '%Y-%m-%d' ) = DATE_FORMAT( NOW(), '%Y-%m-%d' )                                                        "
            + "		LEFT JOIN (                                                                                                                    "
            + "		SELECT CODE                                                                                                                    "
            + "			,                                                                                                                          "
            + "			sku_id,                                                                                                                    "
            + "			price,                                                                                                                     "
            + "			price_dt,                                                                                                                  "
            + "			c_time,                                                                                                                    "
            + "			row_number ( ) over ( PARTITION BY sku_id ORDER BY price_dt DESC, c_time DESC ) AS row_num                                 "
            + "		FROM                                                                                                                           "
            + "			b_goods_price                                                                                                              "
            + "		) t5 ON t1.id = t5.sku_id                                                                                                      "
            + "		AND t5.row_num = 1                                                                                                             "
            + "		AND DATE_FORMAT( t5.price_dt, '%Y-%m-%d' ) <= DATE_FORMAT( NOW(), '%Y-%m-%d' )                                                 "
    })
    public void insertMaterialPrice13();

    // 每日商品价格 b_material_price
    @Update({""
            + "	insert into b_material_price(                                                                                                      "
            + "		goods_id,                                                                                                                      "
            + "		goods_code,                                                                                                                    "
            + "		goods_name,                                                                                                                    "
            + "		sku_id,                                                                                                                        "
            + "		sku_code,                                                                                                                      "
            + "		sku_name,                                                                                                                      "
            + "		type,                                                                                                                          "
            + "		query_code,                                                                                                                    "
            + "		price,                                                                                                                         "
            + "		c_time  )                                                                                                                      "
            + "	SELECT                                                                                                                             "
            + "		t1.goods_id,                                                                                                                   "
            + "		t2.CODE goods_code,                                                                                                            "
            + "		t2.NAME goods_name,                                                                                                            "
            + "		t1.id sku_id,                                                                                                                  "
            + "		t1.CODE sku_code,                                                                                                              "
            + "		t1.spec sku_name,                                                                                                              "
            + "	CASE                                                                                                                               "
            + "			WHEN t4.source_sku_id IS NULL THEN                                                                                         "
            + "			'2'                                                                                                                        "
            + "			WHEN t4.source_sku_id = t4.sku_id THEN                                                                                     "
            + "			'0'                                                                                                                        "
            + "			WHEN t4.source_sku_id <> t4.sku_id THEN                                                                                    "
            + "			'1'                                                                                                                        "
            + "		END type,                                                                                                                      "
            + "	CASE	                                                                                                                           "
            + "			WHEN t4.source_sku_id IS NULL THEN                                                                                         "
            + "			t4.CODE                                                                                                                    "
            + "			WHEN t4.source_sku_id = t4.sku_id THEN                                                                                     "
            + "			t4.CODE                                                                                                                    "
            + "			WHEN t4.source_sku_id <> t4.sku_id THEN                                                                                    "
            + "			t5.CODE                                                                                                                    "
            + "		END query_code,                                                                                                                "
            + "	IFNULL(CASE                                                                                                                        "
            + "			WHEN t4.source_sku_id IS NULL THEN                                                                                         "
            + "			t5.price                                                                                                                   "
            + "			WHEN t4.source_sku_id = t4.sku_id THEN                                                                                     "
            + "			t4.price                                                                                                                   "
            + "			WHEN t4.source_sku_id <> t4.sku_id THEN                                                                                    "
            + "			t4.price                                                                                                                   "
            + "		END , 0) price,                                                                                                                "
            + "		NOW() c_time                                                                                                                   "
            + "	FROM                                                                                                                               "
            + "		m_goods_spec t1                                                                                                                "
            + "		LEFT JOIN m_goods t2 ON t1.goods_id = t2.id                                                                                    "
            + "		LEFT JOIN m_goods_spec_prop t3 ON t1.prop_id = t3.id                                                                           "
            + "		LEFT JOIN b_material_convert_price t4 ON t1.id = t4.sku_id                                                                     "
            + "		AND DATE_FORMAT( t4.dt, '%Y-%m-%d' ) = DATE_FORMAT( NOW(), '%Y-%m-%d' )                                                        "
            + "		LEFT JOIN (                                                                                                                    "
            + "		SELECT CODE                                                                                                                    "
            + "			,                                                                                                                          "
            + "			sku_id,                                                                                                                    "
            + "			price,                                                                                                                     "
            + "			price_dt,                                                                                                                  "
            + "			c_time,                                                                                                                    "
            + "			row_number ( ) over ( PARTITION BY sku_id ORDER BY price_dt DESC, c_time DESC ) AS row_num                                 "
            + "		FROM                                                                                                                           "
            + "			b_goods_price                                                                                                              "
            + "		) t5 ON t1.id = t5.sku_id                                                                                                      "
            + "		AND t5.row_num = 1                                                                                                             "
            + "		AND DATE_FORMAT( t5.price_dt, '%Y-%m-%d' ) <= DATE_FORMAT( NOW(), '%Y-%m-%d' )                                                 "
    })
    public void insertMaterialPrice14();

    @Select({
            "   select                                                                                                  "
            + "	    id,                                                                                                 "
            + "	    goods_id,                                                                                           "
            + "	    goods_code,                                                                                         "
            + "	    goods_name,                                                                                         "
            + "	    sku_id,                                                                                             "
            + "	    sku_code,                                                                                           "
            + "	    sku_name,                                                                                           "
            + "	    type,                                                                                               "
            + "	    query_code,                                                                                         "
            + "	    ifnull(price, 0) price,                                                                             "
            + "	    c_time						                                                                        "
            + "  from b_material_price                                                                                  "
    })
    public List<ApiBMaterialPriceVo> getMaterialPriceList();

}
